const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔧 从设备数据重建实例表');
console.log('========================');

try {
  // 1. 获取所有设备数据
  const devices = db.prepare(`
    SELECT DISTINCT classification_name 
    FROM devices 
    WHERE classification_name IS NOT NULL 
    AND classification_name != ''
    ORDER BY classification_name
  `).all();
  
  console.log(`📊 找到 ${devices.length} 种设备类型`);
  
  // 2. 获取设备类型映射
  const deviceMappings = db.prepare(`
    SELECT classification_name, specialty, naming_table, position_table, base_params_table
    FROM device_type_mappings
    ORDER BY specialty, classification_name
  `).all();
  
  console.log(`📋 设备类型映射: ${deviceMappings.length} 个`);
  
  // 3. 获取三个标准属性表的字段结构
  const namingFields = db.prepare(`PRAGMA table_info(table_c2)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  const positionFields = db.prepare(`PRAGMA table_info(table_c4)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  const positionFields2 = db.prepare(`PRAGMA table_info(table_c6)`).all()
    .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
  
  console.log(`📋 标准字段结构:`);
  console.log(`  命名表字段 (table_c2): ${namingFields.length}个`);
  console.log(`  位置表字段 (table_c4): ${positionFields.length}个`);
  console.log(`  位置表字段 (table_c6): ${positionFields2.length}个`);
  
  // 4. 专业名称映射
  const specialtyMapping = {
    'piping': '配管专业',
    'electrical': '电气专业', 
    'hvac': '暖通专业',
    'communication': '通信专业',
    'instrumentation': '仪表专业'
  };
  
  // 5. 预定义的实例表结构
  const predefinedTables = [
    // Communication tables
    { name: 'device_instances_communication_broadcasting_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_computer_network_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_entrance_exit_management_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_fire_alarm_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_industrial_tv_monitoring_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_patrol_inspection_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_perimeter_intrusion_alarm_equipment', specialty: 'communication' },
    { name: 'device_instances_communication_station_control_system', specialty: 'communication' },
    
    // Electrical tables
    { name: 'device_instances_electrical_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_down_conductor_disconnect_clamp_installation_material', specialty: 'electrical' },
    { name: 'device_instances_electrical_dual_power_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_emergency_power_supply', specialty: 'electrical' },
    { name: 'device_instances_electrical_explosion_proof_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_explosion_proof_lighting_fixture', specialty: 'electrical' },
    { name: 'device_instances_electrical_fuel_generator_equipment', specialty: 'electrical' },
    { name: 'device_instances_electrical_general_lighting_fixture', specialty: 'electrical' },
    { name: 'device_instances_electrical_grounding_system', specialty: 'electrical' },
    { name: 'device_instances_electrical_hot_dip_galvanized_angle_steel_grounding_electrode', specialty: 'electrical' },
    { name: 'device_instances_electrical_hot_dip_galvanized_flat_steel_grounding_wire', specialty: 'electrical' },
    { name: 'device_instances_electrical_lighting_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_lighting_equipment', specialty: 'electrical' },
    { name: 'device_instances_electrical_local_equipotential_bonding_terminal_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_low_voltage_switchgear', specialty: 'electrical' },
    { name: 'device_instances_electrical_main_equipotential_bonding_terminal_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_power_transformer', specialty: 'electrical' },
    { name: 'device_instances_electrical_pxmr_concealed_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_type_a_emergency_lighting_distribution_box', specialty: 'electrical' },
    { name: 'device_instances_electrical_uninterruptible_power_supply', specialty: 'electrical' },
    
    // HVAC tables  
    { name: 'device_instances_hvac_air_conditioning_equipment', specialty: 'hvac' },
    
    // Instrumentation tables
    { name: 'device_instances_instrumentation_access_control_equipment', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_audio_visual_alarm', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_combustible_gas_detector', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_combustible_toxic_gas_detection_alarm_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_computer_network_equipment', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_control_valve', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_controller', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_coriolis_mass_flowmeter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_differential_pressure_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_electric_actuator', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_electro_hydraulic_actuator', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_electromagnetic_flowmeter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_fire_control_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_flame_detector', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_grounding_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_heat_detector', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_instrumentation_and_control_equipment', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_leak_detection_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_level_switch', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_level_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_magnetostrictive_level_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_online_process_analyzer', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_pressure_differential_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_pressure_gauge', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_pressure_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_safety_instrumented_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_scada_system', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_servo_level_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_temperature_transmitter', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_thermometer', specialty: 'instrumentation' },
    { name: 'device_instances_instrumentation_ultrasonic_level_switch', specialty: 'instrumentation' },
    
    // Piping tables
    { name: 'device_instances_piping_air_foam_generator', specialty: 'piping' },
    { name: 'device_instances_piping_ball_valve', specialty: 'piping' },
    { name: 'device_instances_piping_breathing_valve', specialty: 'piping' },
    { name: 'device_instances_piping_butterfly_valve', specialty: 'piping' },
    { name: 'device_instances_piping_centrifugal_pump', specialty: 'piping' },
    { name: 'device_instances_piping_check_valve', specialty: 'piping' },
    { name: 'device_instances_piping_cylindrical_tank', specialty: 'piping' },
    { name: 'device_instances_piping_domestic_wastewater_treatment_equipment', specialty: 'piping' },
    { name: 'device_instances_piping_fire_protection_equipment', specialty: 'piping' },
    { name: 'device_instances_piping_fire_protection_pressure_stabilizing_equipment', specialty: 'piping' },
    { name: 'device_instances_piping_flame_arrester', specialty: 'piping' },
    { name: 'device_instances_piping_flexible_hose', specialty: 'piping' },
    { name: 'device_instances_piping_foam_proportioning_system', specialty: 'piping' },
    { name: 'device_instances_piping_gate_valve', specialty: 'piping' },
    { name: 'device_instances_piping_hydraulic_control_valve', specialty: 'piping' },
    { name: 'device_instances_piping_loading_arm', specialty: 'piping' },
    { name: 'device_instances_piping_metal_bellows', specialty: 'piping' },
    { name: 'device_instances_piping_oily_wastewater_treatment_equipment', specialty: 'piping' },
    { name: 'device_instances_piping_pipeline', specialty: 'piping' },
    { name: 'device_instances_piping_pipeline_filter', specialty: 'piping' },
    { name: 'device_instances_piping_rotor_pump', specialty: 'piping' },
    { name: 'device_instances_piping_safety_valve', specialty: 'piping' },
    { name: 'device_instances_piping_skid', specialty: 'piping' },
    { name: 'device_instances_piping_sliding_vane_pump', specialty: 'piping' },
    { name: 'device_instances_piping_submersible_pump', specialty: 'piping' },
    { name: 'device_instances_piping_valve', specialty: 'piping' }
  ];
  
  console.log(`📋 预定义实例表: ${predefinedTables.length} 个`);
  
  // 6. 为每个预定义表创建标准结构
  let successCount = 0;
  let failCount = 0;
  
  for (const tableConfig of predefinedTables) {
    console.log(`\\n🔧 创建实例表: ${tableConfig.name}`);
    console.log('='.repeat(50));
    
    try {
      // 查找对应的设备类型映射
      const chineseSpecialty = specialtyMapping[tableConfig.specialty];
      let deviceMapping = null;
      
      if (chineseSpecialty) {
        const specialtyMappings = deviceMappings.filter(m => m.specialty === chineseSpecialty);
        if (specialtyMappings.length > 0) {
          deviceMapping = specialtyMappings[0];
        }
      }
      
      if (!deviceMapping) {
        console.log(`  ⚠️  未找到 ${tableConfig.specialty} 专业的设备类型映射，使用默认配置`);
        deviceMapping = {
          naming_table: 'table_c2',
          position_table: 'table_c4', 
          base_params_table: 'table_c2'
        };
      } else {
        console.log(`  ✅ 使用设备映射: ${deviceMapping.classification_name}`);
      }
      
      // 获取基础参数表字段
      let baseParamsFields = [];
      try {
        baseParamsFields = db.prepare(`PRAGMA table_info(${deviceMapping.base_params_table})`).all()
          .filter(col => !['id', 'created_at', 'updated_at'].includes(col.name));
      } catch (error) {
        console.log(`  ⚠️  无法获取基础参数表，使用默认字段`);
        baseParamsFields = [
          {name: '规格', type: 'TEXT'},
          {name: '型号', type: 'TEXT'},
          {name: '制造厂家', type: 'TEXT'}
        ];
      }
      
      // 生成新的表结构
      const newColumns = [];
      
      // 添加业务字段
      newColumns.push('device_type_code TEXT');
      newColumns.push('instance_number TEXT');
      newColumns.push('object_number TEXT');
      newColumns.push('object_description TEXT');
      
      // 添加命名表字段（naming_前缀）
      namingFields.forEach(field => {
        const fieldName = `naming_${field.name}`;
        newColumns.push(`[${fieldName}] ${field.type}`);
      });
      
      // 添加位置表字段（position_前缀）
      const positionTable = deviceMapping.position_table;
      if (positionTable === 'table_c4') {
        positionFields.forEach(field => {
          const fieldName = `position_${field.name}`;
          newColumns.push(`[${fieldName}] ${field.type}`);
        });
      } else if (positionTable === 'table_c6') {
        positionFields2.forEach(field => {
          const fieldName = `position_${field.name}`;
          newColumns.push(`[${fieldName}] ${field.type}`);
        });
      }
      
      // 添加基础参数表字段（base_params_前缀）
      baseParamsFields.forEach(field => {
        const fieldName = `base_params_${field.name}`;
        newColumns.push(`[${fieldName}] ${field.type}`);
      });
      
      // 创建表
      const createTableSQL = `
        CREATE TABLE [${tableConfig.name}] (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          ${newColumns.join(',\\n          ')},
          created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
          updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        )
      `;
      
      db.prepare(createTableSQL).run();
      console.log(`  ✅ 成功创建，总字段: ${newColumns.length + 3}个`);
      
      successCount++;
      
    } catch (error) {
      console.log(`  ❌ 创建 ${tableConfig.name} 失败: ${error.message}`);
      failCount++;
    }
  }
  
  // 7. 总结
  console.log('\\n📊 实例表创建总结');
  console.log('========================');
  console.log(`✅ 成功创建: ${successCount}个表`);
  console.log(`❌ 创建失败: ${failCount}个表`);
  console.log(`📊 总计: ${predefinedTables.length}个表`);
  
  if (successCount > 0) {
    console.log('\\n🎯 创建后的标准结构:');
    console.log('- 系统字段: id, created_at, updated_at');
    console.log('- 业务字段: device_type_code, instance_number, object_number, object_description');
    console.log(`- 命名字段: ${namingFields.length}个 naming_ 前缀字段`);
    console.log('- 位置字段: 10-12个 position_ 前缀字段');
    console.log('- 基础参数字段: 变量个数 base_params_ 前缀字段');
  }
  
  if (successCount === predefinedTables.length) {
    console.log('\\n🎉 所有实例表创建完成！');
    console.log('现在所有实例表都具有完整的前缀命名结构');
  }
  
} catch (error) {
  console.error('❌ 脚本执行失败:', error.message);
} finally {
  db.close();
}